Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE public.customerorder (
  order_id serial NOT NULL
, customer_id int NOT NULL
, order_placed_on timestamptz NOT NULL
, order_description varchar(180) NOT NULL
);

INSERT INTO public.customerorder VALUES
  (1, 2579927, '2022-11-15 16:56:48.750 +0100', 'TEST SAMPLE')
, (2, 2579927, '2022-11-16 16:56:48.750 +0100', 'TEST SAMPLE')
, (3, 2579927, '2022-08-03 16:56:48.750 +0100', 'TEST SAMPLE')
, (4, 2579927, '2022-08-01 16:56:48.750 +0100', 'TEST SAMPLE')
;

CREATE TABLE



INSERT 0 4


CREATE OR REPLACE FUNCTION public.customer_orders(
        _customer_id int
      , _sort_field_and_direction text
      , _limit int default 10
      , _offset int default 0
      , _start_period date = CURRENT_DATE - 30  -- !!!
      , _end_period   date = CURRENT_DATE       -- !!!
      )
  RETURNS TABLE (id int, customerid int, description varchar, placedon timestamptz)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql        text := '
   SELECT order_id, customer_id, order_description, order_placed_on
   FROM   customerorder
   WHERE  customer_id = $1
   AND    order_placed_on BETWEEN $2 AND $3
   ORDER  BY ' || _sort_field_and_direction || '
   LIMIT  $4
   OFFSET $5';
BEGIN
   RAISE NOTICE '%', _sql;

   RETURN QUERY EXECUTE _sql
   USING _customer_id   -- $1
       , _start_period  -- $2
       , _end_period    -- $3
       , _limit         -- $4
       , _offset        -- $5
   ;
END
$func$;

CREATE FUNCTION


SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', default, 2);

ERROR:  DEFAULT is not allowed in this context
LINE 1: ... customer_orders(2579927, 'order_placed_on DESC', default, 2...
                                                             ^


-- run with default period (last 30 days)
SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', 10, 0);


id
customerid
description
placedon



SELECT 0


-- run with custom period
SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', 10, 0, '2022-08-01', '2022-08-11');


id
customerid
description
placedon


3
2579927
TEST SAMPLE
2022-08-03 16:56:48.75+01

4
2579927
TEST SAMPLE
2022-08-01 16:56:48.75+01



SELECT 2

id	customerid	description	placedon
3	2579927	TEST SAMPLE	2022-08-03 16:56:48.75+01
4	2579927	TEST SAMPLE	2022-08-01 16:56:48.75+01